package com.cloud.hotline.api.provider;

import com.cloud.hotline.api.bean.HotlineCalculateForm;
import com.cloud.hotline.api.consts.HotLineConsts;
import com.cloud.hotline.common.utils.DateHelper;

public class HotlineCalculateAreaProvider {
	public static final String SELECT = "select @rownum:=@rownum+1 AS pm, t.* ,truncate(t.xyl*0.3 + t.jjl *0.35 + t.myl*0.35, 2) as zhpf , t.myl+t.jjl as slhj, t.myl+t.jjl - 130 as yjscz from ("
			+ "select (SELECT @rownum:=0) r, unit_name as dw, count(1) as yxf, 100 as xyl, sum(jjs) as jjs, truncate(sum(jjs) / count(1) *100,2) as jjl, sum(mys) as mys, truncate(sum(mys) / count(1) *100,2) as myl "
			+ "from (select unit_name, if(finish_flag_name = '解决', 1, 0) as jjs, if(pleased_flag_name = '满意', 1, 0) as mys from to_hotline_record where 1=1 ";
	private static final String GROUP = ") t group by unit_name) t order by slhj desc";

	public String caculate(HotlineCalculateForm form) {
		StringBuilder sqlBuilder = new StringBuilder(SELECT);
		//考核类型：街道  / 部门
		sqlBuilder.append(" and unit_type = ").append(form.getKhType());
		//考核时间
		if(null != form.getStart()){
			sqlBuilder.append(" and dispatch_date > str_to_date('").append(DateHelper.dateTimeToStr(form.getMaxCityRevisitDate())).append("','%Y-%m-%d %H:%i:%s')");
			sqlBuilder.append(" and dispatch_date > str_to_date('").append(DateHelper.dateTimeToStr(form.getMaxCityRevisitDate())).append("','%Y-%m-%d %H:%i:%s')");
			sqlBuilder.append(" and revisit_date > str_to_date('").append(DateHelper.dateTimeToStr(form.getMaxCityRevisitDate())).append("','%Y-%m-%d %H:%i:%s')");
		}
		if(null != form.getEnd()){
			sqlBuilder.append(" and dispatch_date <= str_to_date('").append(DateHelper.dateTimeToStr(form.getEnd())).append("','%Y-%m-%d %H:%i:%s')");
			sqlBuilder.append(" and revisit_date <= str_to_date('").append(DateHelper.dateTimeToStr(form.getEnd())).append("','%Y-%m-%d %H:%i:%s')");
		}
		// 回访类型： 区级回访，市级别回访
		sqlBuilder.append(" and data_type_id = " + HotLineConsts.DATA_TYPE_AREA_REVISIT);
		sqlBuilder.append(GROUP);
		return sqlBuilder.toString();
	}
}
